
using Youz.Common.DBUtility;
using Youz.CreditRepay.Entity;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Youz.CreditRepay.DAL
{
    /// <summary>
    /// Member_Info Data Class
    /// </summary>
    public partial class Member_InfoDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Member_Info";

        public Member_InfoDAL()
        { }

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Member_Info model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("insert into [Member_Info](");
            strSql.Append("[MemberAccount],[LoginPwd],[CapitalPwd],[Email],[MobileNo],[Status],[LastName],[FirstName],[Sex],[CredentialsType],[CredentialsNum],[Area],[CreateTime],[Referrer],[ReferrerCode]");
            strSql.Append(") values (");
            strSql.Append("@MemberAccount,@LoginPwd,@CapitalPwd,@Email,@MobileNo,@Status,@LastName,@FirstName,@Sex,@CredentialsType,@CredentialsNum,@Area,@CreateTime,@Referrer,@ReferrerCode");
            strSql.Append(")");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@LoginPwd", model.LoginPwd),
                    new SqlParameter("@CapitalPwd", model.CapitalPwd),
                    new SqlParameter("@Email", model.Email),
                    new SqlParameter("@MobileNo", model.MobileNo),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@LastName", model.LastName),
                    new SqlParameter("@FirstName", model.FirstName),
                    new SqlParameter("@Sex", model.Sex),
                    new SqlParameter("@CredentialsType", model.CredentialsType),
                    new SqlParameter("@CredentialsNum", model.CredentialsNum),
                    new SqlParameter("@Area", model.Area),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@ReferrerCode", model.ReferrerCode),
                    new SqlParameter("@Referrer", model.Referrer)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Member_Info model)
        {
            model.ModifyTime = DateTime.Now;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update [Member_Info] set ");
            strSql.Append("[Email]=@Email,[MobileNo]=@MobileNo,[LoginPwd]=@LoginPwd,[CapitalPwd]=@CapitalPwd,[Status]=@Status,[LastName]=@LastName,[FirstName]=@FirstName,[Sex]=@Sex,[CredentialsType]=@CredentialsType,[CredentialsNum]=@CredentialsNum,[Area]=@Area,[ModifyTime]=@ModifyTime");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@Email", model.Email),
                    new SqlParameter("@MobileNo", model.MobileNo),
                    new SqlParameter("@Status", model.Status),
                    new SqlParameter("@LastName", model.LastName),
                    new SqlParameter("@FirstName", model.FirstName),
                    new SqlParameter("@Sex", model.Sex),
                    new SqlParameter("@LoginPwd", model.LoginPwd),
                    new SqlParameter("@CapitalPwd", model.CapitalPwd),
                    new SqlParameter("@CredentialsType", model.CredentialsType),
                    new SqlParameter("@CredentialsNum", model.CredentialsNum),
                    new SqlParameter("@Area", model.Area),
                    new SqlParameter("@ModifyTime", model.ModifyTime)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Member_Info] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
            
                new SqlParameter("@MemberAccount", MemberAccount)             
              
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Info Get(String MemberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select  top 1 * from [Member_Info] ");
            strSql.Append(" where [MemberAccount]=@MemberAccount");

            SqlParameter[] parameters = {
            
                new SqlParameter("MemberAccount", MemberAccount)             
              
            };

            parameters[0].DbType = DbType.String;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_Info Get(DataRow row)
        {
            if (row == null) return null;

            Member_Info model = new Member_Info();


            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }

            if (row["LoginPwd"] != DBNull.Value)
            {
                model.LoginPwd = (String)row["LoginPwd"];
            }

            if (row["CapitalPwd"] != DBNull.Value)
            {
                model.CapitalPwd = (String)row["CapitalPwd"];
            }

            if (row["ErrorTimes"] != DBNull.Value)
            {
                model.ErrorTimes = (Int32)row["ErrorTimes"];
            }

            if (row["ErrorDateTime"] != DBNull.Value)
            {
                model.ErrorDateTime = (DateTime)row["ErrorDateTime"];
            }

            if (row["Email"] != DBNull.Value)
            {
                model.Email = (String)row["Email"];
            }

            if (row["MobileNo"] != DBNull.Value)
            {
                model.MobileNo = (String)row["MobileNo"];
            }

            if (row["Status"] != DBNull.Value)
            {
                model.Status = (MemberStatus)row["Status"];
            }

            if (row["LastName"] != DBNull.Value)
            {
                model.LastName = (String)row["LastName"];
            }

            if (row["FirstName"] != DBNull.Value)
            {
                model.FirstName = (String)row["FirstName"];
            }

            if (row["Nickname"] != DBNull.Value)
            {
                model.Nickname = (String)row["Nickname"];
            }

            if (row["Referrer"] != DBNull.Value)
            {
                model.Referrer = (String)row["Referrer"];
            }

            if (row["ReferrerCode"] != DBNull.Value)
            {
                model.ReferrerCode = (Int64)row["ReferrerCode"];
            }

            if (row["Sex"] != DBNull.Value)
            {
                model.Sex = (Int32)row["Sex"];
            }

            if (row["CredentialsType"] != DBNull.Value)
            {
                model.CredentialsType = (String)row["CredentialsType"];
            }

            if (row["CredentialsNum"] != DBNull.Value)
            {
                model.CredentialsNum = (String)row["CredentialsNum"];
            }

            if (row["Area"] != DBNull.Value)
            {
                model.Area = (String)row["Area"];
            }

            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }

            if (row["ModifyTime"] != DBNull.Value)
            {
                model.ModifyTime = (DateTime)row["ModifyTime"];
            }


            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from [Member_Info] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Member_Info] ");

            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@" SELECT a.* ,b.AreaName
                          FROM [Member_Info] a left join [Member_Area] b on a.Area=convert(nvarchar(20),b.AreaID)  ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by createTime desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        #endregion  成员方法

        /// <summary>
        /// 激活会员帐号
        /// </summary>
        /// <param name="memberAccount">会员帐号</param>
        public bool AcitvateAccount(string memberAccount)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("UPDATE [Member_Info] SET Status = 1 WHERE MemberAccount = @MemberAccount AND Status = 0");

            SqlParameter[] parameters = {
                    new SqlParameter("@MemberAccount", SqlDbType.VarChar,100)
            };

            parameters[0].Value = memberAccount;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 用户是否被锁定或禁用
        /// </summary>
        /// <param name="memberAccount"></param>
        /// <returns></returns>
        public bool IsLocked(string memberAccount)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@MemberAccount", SqlDbType.VarChar,100)};
            parameters[0].Value = memberAccount;

            return DbHelperSQL.Exists(string.Format("SELECT 1 FROM [Member_Info] WHERE MemberAccount = @MemberAccount and [Status] in({0},{1})", (int)MemberStatus.LockedAsset, (int)MemberStatus.LockedLogin), parameters);
        }

        /// <summary>
        /// 锁定用户
        /// </summary>
        /// <param name="memberAccount"></param>
        /// <returns></returns>
        public bool Lock(string memberAccount)
        {
            SqlParameter[] parameters = {
                         new SqlParameter("@MemberAccount", memberAccount)
            };

            return DbHelperSQL.ExecuteSql(string.Format("UPDATE [Member_Info] SET [Status]={0} WHERE MemberAccount = @MemberAccount", (int)MemberStatus.LockedLogin), parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public string GetMemberAcountByReferrerCode(Int64 ReferrerCode)
        {
            string sql = "select MemberAccount from Member_Info where ReferrerCode = @ReferrerCode";

            SqlParameter[] parameters = {
					new SqlParameter("@ReferrerCode", ReferrerCode)		
										};

            return DbHelperSQL.GetSingle(sql, parameters) as string;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsMobileNo(string MobileNo)
        {
            string sql = "select count(1) from Member_Info where MobileNo = @MobileNo";

            SqlParameter[] parameters = {
					new SqlParameter("@MobileNo", MobileNo)		
										};

            return DbHelperSQL.Exists(sql, parameters);
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsEmail(string Email)
        {
            string sql = "select count(1) from Member_Info where Email = @Email";

            SqlParameter[] parameters = {
					new SqlParameter("@Email", Email)			
										};

            return DbHelperSQL.Exists(sql, parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsMobileNo(string MobileNo, string MemberAccount)
        {
            string sql = "select count(1) from Member_Info where MobileNo = @MobileNo and MemberAccount!=@MemberAccount";

            SqlParameter[] parameters = {
					new SqlParameter("@MobileNo", MobileNo)	,
		            new SqlParameter("@MemberAccount", MemberAccount)	
										};

            return DbHelperSQL.Exists(sql, parameters);
        }

        #region  会员统计
        /// <summary>
        /// 获得各个区域下的人数
        /// </summary>
        public DataTable AreaNum(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT   b.AreaName,  count(b.AreaName) Num
                             FROM Member_Info a left join Member_Area b on a.Referrer=b.AreaName where Status!=0
                                   ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(sqlWhere);
            }
            strSql.Append(" group by  b.AreaName");

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by Num desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Member_Info] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 活跃量 (登录)
        /// </summary>
        /// <returns></returns>
        public DataSet TodayActiveVolume(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(1)as ActiveVolume ,CONVERT(varchar(100),b.CreateDate, 112)CreateDate ");
              //  strSql.Append(",a.Area,c.AreaName ");        
            strSql.Append("  FROM [Member_Info]a left join [Log_LoginLog] b on a.MemberAccount=b.MemberAccount left join Member_Area c on a.Area=c.AreaID ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 活跃量 (交易)
        /// </summary>
        /// <returns></returns>
        public DataSet TodayTradeActiveVolume(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(1)as ActiveVolume ,CONVERT(varchar(100),b.CreateTime, 112)CreateTime ");
            //  strSql.Append(",a.Area,c.AreaName ");        
            strSql.Append("  FROM [Member_Info]a left join [Member_AssetHistory]b on a.MemberAccount=b.MemberAccount");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        /// <summary>
        /// 会员盈亏分页
        /// </summary>
        public DataTable MemberProfitPager(string sqlWhere, int pageindex, int pagesize, out int total)
        {
            SqlParameter[] param = new SqlParameter[] { 
             new SqlParameter(){ ParameterName="@pageIndex", SqlDbType=SqlDbType.Int, Value= pageindex},
             new SqlParameter(){ ParameterName="@pageSize", SqlDbType=SqlDbType.Int, Value= pagesize},
             new SqlParameter(){ ParameterName="@sqlWhere", SqlDbType=SqlDbType.NVarChar, Value= sqlWhere},
             new SqlParameter(){ ParameterName="@TotalCount", SqlDbType=SqlDbType.Int, Direction= ParameterDirection.Output},
            };

            DataTable dt = DbHelperSQL.RunProcedure("USP_MemberProfitPager", param).Tables[0];
            total = Convert.ToInt32(param[3].Value);
            return dt;
        }

        public void UpdateAppID(string memberAccount, string appID)
        {
            DbHelperSQL.ExecuteSql(@"update Member_Info set [AppID]=null where [AppID]=@AppID
                                     update Member_Info set [AppID]=@AppID where MemberAccount=@MemberAccount",
                new SqlParameter[] { new SqlParameter("@MemberAccount", memberAccount), new SqlParameter("@AppID", appID) });
        }

        /// <summary>
        /// 登出
        /// </summary>
        /// <param name="memberAccount"></param>
        /// <param name="appID"></param>
        public void LogoutApp(string memberAccount)
        {
            DbHelperSQL.ExecuteSql(@"update Member_Info set [AppID]=null,[ModifyTime]=@ModifyTime where MemberAccount=@MemberAccount",
                new SqlParameter[] { 
                    new SqlParameter("@MemberAccount", memberAccount),
                    new SqlParameter("@ModifyTime", DateTime.Now)
                });
        }

        public void SetNickname(string memberAccount, string nickname)
        {
            DbHelperSQL.ExecuteSql(@"update Member_Info set [Nickname]=@Nickname,[ModifyTime]=@ModifyTime where MemberAccount=@MemberAccount",
                new SqlParameter[] { 
                    new SqlParameter("@MemberAccount", memberAccount),
                    new SqlParameter("@Nickname", nickname),
                    new SqlParameter("@ModifyTime", DateTime.Now)
                });
        }

        public void ChangeLoginPassword(string memberAccount, string password)
        {
            DbHelperSQL.ExecuteSql(@"update Member_Info set [LoginPwd]=@LoginPwd,[ModifyTime]=@ModifyTime where MemberAccount=@MemberAccount",
                new SqlParameter[] { 
                    new SqlParameter("@MemberAccount", memberAccount),
                    new SqlParameter("@LoginPwd", password),
                    new SqlParameter("@ModifyTime", DateTime.Now)
                });
        }

        public void ChangePayPassword(string memberAccount, string password)
        {
            DbHelperSQL.ExecuteSql(@"update Member_Info set [CapitalPwd]=@CapitalPwd,[ModifyTime]=@ModifyTime where MemberAccount=@MemberAccount",
                new SqlParameter[] { 
                    new SqlParameter("@MemberAccount", memberAccount),
                    new SqlParameter("@CapitalPwd", password),
                    new SqlParameter("@ModifyTime", DateTime.Now)
                });
        }

        public Int64 GenerateReferrerCode()
        {
            var id = (Int64)DbHelperSQL.GetSingle("select isnull(max(ReferrerCode),0) from [dbo].[Member_Info]");

            return id == 0 ? 122126 : id + 1;
        }
    }
}


